package com.cencat.merchant.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.cencat.merchant.entity.RouteInvoice;
import com.cencat.merchant.dto.RouteInvoiceQueryDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 路线发票管理Mapper接口
 * 提供路线发票数据的增删改查、统计分析、税务分析、合规性检查等数据访问功能
 * 
 * @author cencat
 * @version 1.0
 * @since 2025-01-20
 */
@Mapper
public interface RouteInvoiceMapper extends BaseMapper<RouteInvoice> {

    /**
     * 分页查询路线发票列表
     * 
     * @param page 分页参数
     * @param queryDTO 查询条件
     * @return 路线发票分页数据
     */
    Page<RouteInvoice> selectRouteInvoicePage(Page<RouteInvoice> page, @Param("query") RouteInvoiceQueryDTO queryDTO);

    /**
     * 批量更新发票类型
     * 
     * @param ids 发票ID列表
     * @param invoiceType 发票类型
     * @return 更新记录数
     */
    @Update("<script>" +
            "UPDATE route_invoice SET invoice_type = #{invoiceType}, update_time = NOW() " +
            "WHERE id IN " +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    int batchUpdateInvoiceType(@Param("ids") List<Long> ids, @Param("invoiceType") String invoiceType);

    /**
     * 批量更新发票税率
     * 
     * @param ids 发票ID列表
     * @param taxRate 税率
     * @return 更新记录数
     */
    @Update("<script>" +
            "UPDATE route_invoice SET tax_rate = #{taxRate}, update_time = NOW() " +
            "WHERE id IN " +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    int batchUpdateTaxRate(@Param("ids") List<Long> ids, @Param("taxRate") BigDecimal taxRate);

    /**
     * 批量更新发票金额
     * 
     * @param ids 发票ID列表
     * @param invoiceAmount 发票金额
     * @return 更新记录数
     */
    @Update("<script>" +
            "UPDATE route_invoice SET invoice_amount = #{invoiceAmount}, update_time = NOW() " +
            "WHERE id IN " +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    int batchUpdateInvoiceAmount(@Param("ids") List<Long> ids, @Param("invoiceAmount") BigDecimal invoiceAmount);

    /**
     * 开具发票
     * 
     * @param invoiceId 发票ID
     * @param issueTime 开具时间
     * @param issueUser 开具人
     * @return 更新记录数
     */
    @Update("UPDATE route_invoice SET status = 'ISSUED', issue_time = #{issueTime}, issue_user = #{issueUser}, update_time = NOW() WHERE id = #{invoiceId}")
    int issueInvoice(@Param("invoiceId") Long invoiceId, @Param("issueTime") LocalDateTime issueTime, @Param("issueUser") String issueUser);

    /**
     * 作废发票
     * 
     * @param invoiceId 发票ID
     * @param voidTime 作废时间
     * @param voidUser 作废人
     * @param voidReason 作废原因
     * @return 更新记录数
     */
    @Update("UPDATE route_invoice SET status = 'VOID', void_time = #{voidTime}, void_user = #{voidUser}, void_reason = #{voidReason}, update_time = NOW() WHERE id = #{invoiceId}")
    int voidInvoice(@Param("invoiceId") Long invoiceId, @Param("voidTime") LocalDateTime voidTime, @Param("voidUser") String voidUser, @Param("voidReason") String voidReason);

    /**
     * 红冲发票
     * 
     * @param invoiceId 发票ID
     * @param redRushTime 红冲时间
     * @param redRushUser 红冲人
     * @param redRushReason 红冲原因
     * @return 更新记录数
     */
    @Update("UPDATE route_invoice SET status = 'RED_RUSH', red_rush_time = #{redRushTime}, red_rush_user = #{redRushUser}, red_rush_reason = #{redRushReason}, update_time = NOW() WHERE id = #{invoiceId}")
    int redRushInvoice(@Param("invoiceId") Long invoiceId, @Param("redRushTime") LocalDateTime redRushTime, @Param("redRushUser") String redRushUser, @Param("redRushReason") String redRushReason);

    /**
     * 审核发票
     * 
     * @param invoiceId 发票ID
     * @param auditStatus 审核状态
     * @param auditTime 审核时间
     * @param auditUser 审核人
     * @param auditRemark 审核备注
     * @return 更新记录数
     */
    @Update("UPDATE route_invoice SET audit_status = #{auditStatus}, audit_time = #{auditTime}, audit_user = #{auditUser}, audit_remark = #{auditRemark}, update_time = NOW() WHERE id = #{invoiceId}")
    int auditInvoice(@Param("invoiceId") Long invoiceId, @Param("auditStatus") String auditStatus, @Param("auditTime") LocalDateTime auditTime, @Param("auditUser") String auditUser, @Param("auditRemark") String auditRemark);

    /**
     * 认证发票
     * 
     * @param invoiceId 发票ID
     * @param certifyTime 认证时间
     * @param certifyUser 认证人
     * @return 更新记录数
     */
    @Update("UPDATE route_invoice SET certify_status = 'CERTIFIED', certify_time = #{certifyTime}, certify_user = #{certifyUser}, update_time = NOW() WHERE id = #{invoiceId}")
    int certifyInvoice(@Param("invoiceId") Long invoiceId, @Param("certifyTime") LocalDateTime certifyTime, @Param("certifyUser") String certifyUser);

    /**
     * 根据发票状态统计发票数量
     * 
     * @param status 发票状态
     * @return 发票数量
     */
    @Select("SELECT COUNT(*) FROM route_invoice WHERE status = #{status}")
    Long countByStatus(@Param("status") String status);

    /**
     * 根据发票类型统计发票数量
     * 
     * @param invoiceType 发票类型
     * @return 发票数量
     */
    @Select("SELECT COUNT(*) FROM route_invoice WHERE invoice_type = #{invoiceType}")
    Long countByInvoiceType(@Param("invoiceType") String invoiceType);

    /**
     * 根据审核状态统计发票数量
     * 
     * @param auditStatus 审核状态
     * @return 发票数量
     */
    @Select("SELECT COUNT(*) FROM route_invoice WHERE audit_status = #{auditStatus}")
    Long countByAuditStatus(@Param("auditStatus") String auditStatus);

    /**
     * 计算发票总金额
     * 
     * @param status 发票状态
     * @return 发票总金额
     */
    @Select("SELECT SUM(invoice_amount) FROM route_invoice WHERE status = #{status}")
    BigDecimal calculateTotalAmount(@Param("status") String status);

    /**
     * 计算税额总额
     * 
     * @param status 发票状态
     * @return 税额总额
     */
    @Select("SELECT SUM(tax_amount) FROM route_invoice WHERE status = #{status}")
    BigDecimal calculateTotalTaxAmount(@Param("status") String status);

    /**
     * 查询待开具发票列表
     * 
     * @param limit 限制数量
     * @return 待开具发票列表
     */
    @Select("SELECT * FROM route_invoice WHERE status = 'PENDING' ORDER BY create_time ASC LIMIT #{limit}")
    List<RouteInvoice> selectPendingInvoices(@Param("limit") Integer limit);

    /**
     * 查询已开具发票列表
     * 
     * @param limit 限制数量
     * @return 已开具发票列表
     */
    @Select("SELECT * FROM route_invoice WHERE status = 'ISSUED' ORDER BY issue_time DESC LIMIT #{limit}")
    List<RouteInvoice> selectIssuedInvoices(@Param("limit") Integer limit);

    /**
     * 查询已作废发票列表
     * 
     * @param limit 限制数量
     * @return 已作废发票列表
     */
    @Select("SELECT * FROM route_invoice WHERE status = 'VOID' ORDER BY void_time DESC LIMIT #{limit}")
    List<RouteInvoice> selectVoidInvoices(@Param("limit") Integer limit);

    /**
     * 查询已红冲发票列表
     * 
     * @param limit 限制数量
     * @return 已红冲发票列表
     */
    @Select("SELECT * FROM route_invoice WHERE status = 'RED_RUSH' ORDER BY red_rush_time DESC LIMIT #{limit}")
    List<RouteInvoice> selectRedRushInvoices(@Param("limit") Integer limit);

    /**
     * 根据路线ID查询发票列表
     * 
     * @param routeId 路线ID
     * @param status 发票状态
     * @param limit 限制数量
     * @return 发票列表
     */
    @Select("<script>" +
            "SELECT * FROM route_invoice WHERE route_id = #{routeId} " +
            "<if test='status != null and status != \"\">" +
            "AND status = #{status} " +
            "</if>" +
            "ORDER BY create_time DESC " +
            "<if test='limit != null'>" +
            "LIMIT #{limit}" +
            "</if>" +
            "</script>")
    List<RouteInvoice> selectInvoicesByRouteId(@Param("routeId") Long routeId, @Param("status") String status, @Param("limit") Integer limit);

    /**
     * 根据商户ID查询发票列表
     * 
     * @param merchantId 商户ID
     * @param status 发票状态
     * @param limit 限制数量
     * @return 发票列表
     */
    @Select("<script>" +
            "SELECT * FROM route_invoice WHERE merchant_id = #{merchantId} " +
            "<if test='status != null and status != \"\">" +
            "AND status = #{status} " +
            "</if>" +
            "ORDER BY create_time DESC " +
            "<if test='limit != null'>" +
            "LIMIT #{limit}" +
            "</if>" +
            "</script>")
    List<RouteInvoice> selectInvoicesByMerchantId(@Param("merchantId") Long merchantId, @Param("status") String status, @Param("limit") Integer limit);

    /**
     * 根据发票类型查询发票列表
     * 
     * @param invoiceType 发票类型
     * @param status 发票状态
     * @param limit 限制数量
     * @return 发票列表
     */
    @Select("<script>" +
            "SELECT * FROM route_invoice WHERE invoice_type = #{invoiceType} " +
            "<if test='status != null and status != \"\">" +
            "AND status = #{status} " +
            "</if>" +
            "ORDER BY create_time DESC " +
            "<if test='limit != null'>" +
            "LIMIT #{limit}" +
            "</if>" +
            "</script>")
    List<RouteInvoice> selectInvoicesByType(@Param("invoiceType") String invoiceType, @Param("status") String status, @Param("limit") Integer limit);

    /**
     * 根据开具时间范围查询发票列表
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param status 发票状态
     * @param limit 限制数量
     * @return 发票列表
     */
    @Select("<script>" +
            "SELECT * FROM route_invoice WHERE issue_time BETWEEN #{startTime} AND #{endTime} " +
            "<if test='status != null and status != \"\">" +
            "AND status = #{status} " +
            "</if>" +
            "ORDER BY issue_time DESC " +
            "<if test='limit != null'>" +
            "LIMIT #{limit}" +
            "</if>" +
            "</script>")
    List<RouteInvoice> selectInvoicesByIssueTime(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, @Param("status") String status, @Param("limit") Integer limit);

    /**
     * 检查发票号码是否存在
     * 
     * @param invoiceNumber 发票号码
     * @param excludeId 排除的发票ID
     * @return 存在数量
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM route_invoice WHERE invoice_number = #{invoiceNumber} " +
            "<if test='excludeId != null'>" +
            "AND id != #{excludeId}" +
            "</if>" +
            "</script>")
    int existsByInvoiceNumber(@Param("invoiceNumber") String invoiceNumber, @Param("excludeId") Long excludeId);

    /**
     * 搜索发票
     * 
     * @param keyword 关键词
     * @param searchType 搜索类型
     * @param limit 限制数量
     * @return 发票列表
     */
    @Select("<script>" +
            "SELECT * FROM route_invoice WHERE 1=1 " +
            "<choose>" +
            "<when test='searchType == \"number\"'>" +
            "AND invoice_number LIKE CONCAT('%', #{keyword}, '%') " +
            "</when>" +
            "<when test='searchType == \"title\"'>" +
            "AND invoice_title LIKE CONCAT('%', #{keyword}, '%') " +
            "</when>" +
            "<when test='searchType == \"buyer\"'>" +
            "AND buyer_name LIKE CONCAT('%', #{keyword}, '%') " +
            "</when>" +
            "<when test='searchType == \"seller\"'>" +
            "AND seller_name LIKE CONCAT('%', #{keyword}, '%') " +
            "</when>" +
            "<otherwise>" +
            "AND (invoice_number LIKE CONCAT('%', #{keyword}, '%') " +
            "OR invoice_title LIKE CONCAT('%', #{keyword}, '%') " +
            "OR buyer_name LIKE CONCAT('%', #{keyword}, '%') " +
            "OR seller_name LIKE CONCAT('%', #{keyword}, '%')) " +
            "</otherwise>" +
            "</choose>" +
            "ORDER BY create_time DESC " +
            "<if test='limit != null'>" +
            "LIMIT #{limit}" +
            "</if>" +
            "</script>")
    List<RouteInvoice> searchInvoices(@Param("keyword") String keyword, @Param("searchType") String searchType, @Param("limit") Integer limit);

    /**
     * 查询发票列表（用于导出）
     * 
     * @param queryDTO 查询条件
     * @return 发票列表
     */
    List<RouteInvoice> selectInvoiceList(@Param("query") RouteInvoiceQueryDTO queryDTO);

    /**
     * 计算指定时间范围内的发票数量
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param status 发票状态
     * @return 发票数量
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM route_invoice " +
            "WHERE create_time BETWEEN #{startTime} AND #{endTime} " +
            "<if test='status != null and status != \"\">" +
            "AND status = #{status}" +
            "</if>" +
            "</script>")
    Integer calculateInvoiceCount(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, @Param("status") String status);

    /**
     * 计算指定时间范围内的发票总金额
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param status 发票状态
     * @return 发票总金额
     */
    @Select("<script>" +
            "SELECT SUM(invoice_amount) FROM route_invoice " +
            "WHERE create_time BETWEEN #{startTime} AND #{endTime} " +
            "<if test='status != null and status != \"\">" +
            "AND status = #{status}" +
            "</if>" +
            "</script>")
    BigDecimal calculateTotalAmountByTime(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, @Param("status") String status);

    /**
     * 计算指定时间范围内的税额总额
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param status 发票状态
     * @return 税额总额
     */
    @Select("<script>" +
            "SELECT SUM(tax_amount) FROM route_invoice " +
            "WHERE create_time BETWEEN #{startTime} AND #{endTime} " +
            "<if test='status != null and status != \"\">" +
            "AND status = #{status}" +
            "</if>" +
            "</script>")
    BigDecimal calculateTotalTaxAmountByTime(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, @Param("status") String status);

    /**
     * 获取发票趋势数据
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param trendType 趋势类型
     * @return 趋势数据
     */
    List<Map<String, Object>> selectInvoiceTrends(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, @Param("trendType") String trendType);

    /**
     * 获取发票排行榜
     * 
     * @param rankType 排行类型
     * @param limit 限制数量
     * @return 排行榜数据
     */
    List<Map<String, Object>> selectInvoiceRanking(@Param("rankType") String rankType, @Param("limit") Integer limit);

    /**
     * 获取发票分布数据
     * 
     * @param distributionType 分布类型
     * @return 分布数据
     */
    List<Map<String, Object>> selectInvoiceDistribution(@Param("distributionType") String distributionType);

    /**
     * 获取发票统计数据
     * 
     * @param invoiceId 发票ID
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 统计数据
     */
    Map<String, Object> selectInvoiceStats(@Param("invoiceId") Long invoiceId, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 获取税务统计数据
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param taxType 税务类型
     * @return 税务统计数据
     */
    Map<String, Object> selectTaxStats(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, @Param("taxType") String taxType);

    /**
     * 获取合规性检查数据
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param complianceType 合规性类型
     * @return 合规性检查数据
     */
    Map<String, Object> selectComplianceStats(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, @Param("complianceType") String complianceType);

    /**
     * 根据金额范围查询发票列表
     * 
     * @param minAmount 最小金额
     * @param maxAmount 最大金额
     * @param status 发票状态
     * @param limit 限制数量
     * @return 发票列表
     */
    @Select("<script>" +
            "SELECT * FROM route_invoice WHERE invoice_amount BETWEEN #{minAmount} AND #{maxAmount} " +
            "<if test='status != null and status != \"\">" +
            "AND status = #{status} " +
            "</if>" +
            "ORDER BY invoice_amount DESC " +
            "<if test='limit != null'>" +
            "LIMIT #{limit}" +
            "</if>" +
            "</script>")
    List<RouteInvoice> selectInvoicesByAmountRange(@Param("minAmount") BigDecimal minAmount, @Param("maxAmount") BigDecimal maxAmount, @Param("status") String status, @Param("limit") Integer limit);

    /**
     * 根据税率范围查询发票列表
     * 
     * @param minTaxRate 最小税率
     * @param maxTaxRate 最大税率
     * @param status 发票状态
     * @param limit 限制数量
     * @return 发票列表
     */
    @Select("<script>" +
            "SELECT * FROM route_invoice WHERE tax_rate BETWEEN #{minTaxRate} AND #{maxTaxRate} " +
            "<if test='status != null and status != \"\">" +
            "AND status = #{status} " +
            "</if>" +
            "ORDER BY tax_rate ASC " +
            "<if test='limit != null'>" +
            "LIMIT #{limit}" +
            "</if>" +
            "</script>")
    List<RouteInvoice> selectInvoicesByTaxRateRange(@Param("minTaxRate") BigDecimal minTaxRate, @Param("maxTaxRate") BigDecimal maxTaxRate, @Param("status") String status, @Param("limit") Integer limit);

    /**
     * 批量更新发票状态
     * 
     * @param ids 发票ID列表
     * @param status 新状态
     * @return 更新记录数
     */
    @Update("<script>" +
            "UPDATE route_invoice SET status = #{status}, update_time = NOW() " +
            "WHERE id IN " +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    int batchUpdateStatus(@Param("ids") List<Long> ids, @Param("status") String status);

    /**
     * 批量更新审核状态
     * 
     * @param ids 发票ID列表
     * @param auditStatus 审核状态
     * @param auditUser 审核人
     * @param auditTime 审核时间
     * @return 更新记录数
     */
    @Update("<script>" +
            "UPDATE route_invoice SET audit_status = #{auditStatus}, audit_user = #{auditUser}, audit_time = #{auditTime}, update_time = NOW() " +
            "WHERE id IN " +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    int batchUpdateAuditStatus(@Param("ids") List<Long> ids, @Param("auditStatus") String auditStatus, @Param("auditUser") String auditUser, @Param("auditTime") LocalDateTime auditTime);

    /**
     * 更新发票认证状态
     * 
     * @param invoiceId 发票ID
     * @param certifyStatus 认证状态
     * @return 更新记录数
     */
    @Update("UPDATE route_invoice SET certify_status = #{certifyStatus}, update_time = NOW() WHERE id = #{invoiceId}")
    int updateCertifyStatus(@Param("invoiceId") Long invoiceId, @Param("certifyStatus") String certifyStatus);

    /**
     * 获取商户发票统计
     * 
     * @param merchantId 商户ID
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 商户发票统计数据
     */
    Map<String, Object> selectMerchantInvoiceStats(@Param("merchantId") Long merchantId, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 获取路线发票统计
     * 
     * @param routeId 路线ID
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 路线发票统计数据
     */
    Map<String, Object> selectRouteInvoiceStats(@Param("routeId") Long routeId, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 获取发票类型统计
     * 
     * @param invoiceType 发票类型
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 发票类型统计数据
     */
    Map<String, Object> selectInvoiceTypeStats(@Param("invoiceType") String invoiceType, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 获取税务分析数据
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param analysisType 分析类型
     * @return 税务分析数据
     */
    List<Map<String, Object>> selectTaxAnalysis(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, @Param("analysisType") String analysisType);

    /**
     * 获取合规性分析数据
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param complianceRule 合规性规则
     * @return 合规性分析数据
     */
    List<Map<String, Object>> selectComplianceAnalysis(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, @Param("complianceRule") String complianceRule);

    /**
     * 获取发票风险分析数据
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param riskLevel 风险等级
     * @return 发票风险分析数据
     */
    List<Map<String, Object>> selectInvoiceRiskAnalysis(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, @Param("riskLevel") String riskLevel);
}